﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CPPEI.Coolzon.Core.Repositories.SQL
{
    public class SqlSolutionRepository : ISolutionRepository
    {
        public PagedList<Data.Solution> GetSolutionList(DbContext context, int start, int limit)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                    {
                        cmd.Connection = (SqlConnection)context.DbConnection;
                        cmd.CommandText = "select * from (select *, ROW_NUMBER() OVER(ORDER BY Id) as rownum from [Solution]) a where a.rownum >= @param1 and a.rownum <= @param2";
                        cmd.Parameters.Add(new SqlParameter("param1", start));
                        cmd.Parameters.Add(new SqlParameter("param2", start + limit));

                        DataSet ds = new DataSet();
                        sda.Fill(ds);
                        List<Data.Solution> solutionList = null;
                        if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                        {
                            solutionList = new List<Data.Solution>();
                            foreach (DataRow dr in ds.Tables[0].Rows)
                            {
                                Data.Solution solution = new Data.Solution();
                                solution.Id = new Guid(dr["Id"].ToString());
                                solution.Name = dr["Name"].ToString();
                                solution.Caption = dr["Caption"].ToString();
                                solution.Type = dr["Type"].ToString();
                                solution.IsEnable = bool.Parse(dr["IsEnable"].ToString());
                                solutionList.Add(solution);
                            }
                            return new PagedList<Data.Solution>(solutionList, start, limit, solutionList.Count);
                        }
                        else
                        {
                            return null;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool CreateSolution(DbContext context, Data.Solution solution)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = (SqlConnection)context.DbConnection;
                    cmd.CommandText = "insert into [Solution](Id, Name, Caption, Type, IsEnable) values(@paramId, @paramName, @paramCaption, @paramType, @paramIsEnable)";
                    cmd.Parameters.Add(new SqlParameter("paramId", solution.Id));
                    cmd.Parameters.Add(new SqlParameter("paramName", solution.Name));
                    cmd.Parameters.Add(new SqlParameter("paramCaption", solution.Caption));
                    cmd.Parameters.Add(new SqlParameter("paramType", solution.Type));
                    cmd.Parameters.Add(new SqlParameter("paramIsEnable", solution.IsEnable ? 1 : 0));

                    int ret = cmd.ExecuteNonQuery();
                    if (ret <= 0)
                    {
                        return false;
                    }
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool RemoveSolution(DbContext context, Data.Solution solution)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = (SqlConnection)context.DbConnection;
                    cmd.CommandText = "delete from [Solution] where Id = @paramId";
                    cmd.Parameters.Add(new SqlParameter("paramId", solution.Id));

                    int ret = cmd.ExecuteNonQuery();
                    if (ret <= 0)
                    {
                        return false;
                    }
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool UpdateSolution(DbContext context, Data.Solution solution)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = (SqlConnection)context.DbConnection;
                    cmd.CommandText = "update [Solution] set Name = @paramName, Caption = @paramCaption, Type = @paramType, IsEnable = @paramIsEnable where Id = @paramId";
                    cmd.Parameters.Add(new SqlParameter("paramId", solution.Id));
                    cmd.Parameters.Add(new SqlParameter("paramName", solution.Name));
                    cmd.Parameters.Add(new SqlParameter("paramCaption", solution.Caption));
                    cmd.Parameters.Add(new SqlParameter("paramType", solution.Type));
                    cmd.Parameters.Add(new SqlParameter("paramIsEnable", solution.IsEnable ? 1 : 0));

                    int ret = cmd.ExecuteNonQuery();
                    if (ret <= 0)
                    {
                        return false;
                    }
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 通过solutionId获取solution对象
        /// </summary>
        /// <param name="context"></param>
        /// <param name="solutionId"></param>
        /// <returns></returns>
        public Data.Solution GetSolution(DbContext context, Guid solutionId)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                    {
                        cmd.Connection = (SqlConnection)context.DbConnection;
                        cmd.CommandText = "select * from [Solution] where Id = @paramId";
                        cmd.Parameters.Add(new SqlParameter("paramId", solutionId));

                        DataSet ds = new DataSet();
                        sda.Fill(ds);
                        if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                        {
                            Data.Solution solution = new Data.Solution();
                            solution.Id = new Guid(ds.Tables[0].Rows[0]["Id"].ToString());
                            solution.Name = ds.Tables[0].Rows[0]["Name"].ToString();
                            solution.Caption = ds.Tables[0].Rows[0]["Caption"].ToString();
                            solution.Type = ds.Tables[0].Rows[0]["Type"].ToString();
                            solution.IsEnable = bool.Parse(ds.Tables[0].Rows[0]["IsEnable"].ToString());
                            return solution;
                        }
                        else
                        {
                            return null;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 通过solutionName获取solution对象
        /// </summary>
        /// <param name="context"></param>
        /// <param name="solutionName"></param>
        /// <returns></returns>
        public Data.Solution GetSolution(DbContext context, string solutionName)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                    {
                        cmd.Connection = (SqlConnection)context.DbConnection;
                        cmd.CommandText = "select * from [Solution] where Name = @paramName";
                        cmd.Parameters.Add(new SqlParameter("paramName", solutionName));

                        DataSet ds = new DataSet();
                        sda.Fill(ds);
                        if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                        {
                            Data.Solution solution = new Data.Solution();
                            solution.Id = new Guid(ds.Tables[0].Rows[0]["Id"].ToString());
                            solution.Name = ds.Tables[0].Rows[0]["Name"].ToString();
                            solution.Caption = ds.Tables[0].Rows[0]["Caption"].ToString();
                            solution.Type = ds.Tables[0].Rows[0]["Type"].ToString();
                            solution.IsEnable = bool.Parse(ds.Tables[0].Rows[0]["IsEnable"].ToString());
                            return solution;
                        }
                        else
                        {
                            return null;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
